import javax.swing.*;
import java.util.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import quick.dbtable.*;

/**
 * A JDialog for selecting and deselecting subjects.
 *
 * @author Jacel Evasco
 * @author Roselle Nympha Lim
 * @version 1.0
 */
public class SubjectsChooser extends JDialog {

    private DBTable data = new DBTable();
    private DBTable userData = new DBTable();
    private JPanel fields = new JPanel();
    private JPanel labels = new JPanel();
    private JPanel buttons = new JPanel();
    private JPanel addHolder = new JPanel();
    private JPanel removeHolder = new JPanel();
    private JLabel background = new JLabel(new ImageIcon(
        "images\\backgrounds\\subjectsChooserBG.png"));
    private JComboBox course = new JComboBox();
    private JComboBox year = new JComboBox();
    private JComboBox semester = new JComboBox();
    private JButton okay = new JButton("okay");
    private JButton add = new JButton(new ImageIcon(
        "images\\icons\\subjectAddNormal.png"));
    private JButton remove = new JButton(new ImageIcon(
        "images\\icons\\removeNormal.png"));
    private JLabel[] fieldLabels = { new JLabel("course"),
        new JLabel("year"), new JLabel("semester"),
        new JLabel("selected subjects"), new JLabel("all subjects") };

    Connection connection = null;
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    public SubjectsChooser() {
        assemble();
        configure();
        design();
        setTriggers();
        setVisible(false);
    }// end of GoalEditor()

    private void assemble() {

        // labels
        labels.setLayout(new BoxLayout(labels, BoxLayout.X_AXIS));
        labels.add(Box.createRigidArea(new Dimension(35, 0)));
        labels.add(fieldLabels[0]);
        labels.add(Box.createRigidArea(new Dimension(8, 0)));
        labels.add(fieldLabels[1]);
        labels.add(Box.createRigidArea(new Dimension(8, 0)));
        labels.add(fieldLabels[2]);
        // fields
        fields.setLayout(new BoxLayout(fields, BoxLayout.X_AXIS));
        fields.add(Box.createRigidArea(new Dimension(35, 0)));
        fields.add(course);
        fields.add(Box.createRigidArea(new Dimension(8, 0)));
        fields.add(year);
        fields.add(Box.createRigidArea(new Dimension(8, 0)));
        fields.add(semester);
        // buttons
        buttons.setLayout(new BoxLayout(buttons, BoxLayout.X_AXIS));
        buttons.add(Box.createRigidArea(new Dimension(350, 0)));
        buttons.add(okay);
        // addHolder
        addHolder
            .setLayout(new BoxLayout(addHolder, BoxLayout.X_AXIS));
        addHolder.add(Box.createRigidArea(new Dimension(35, 0)));
        addHolder.add(fieldLabels[4]);
        addHolder.add(Box.createRigidArea(new Dimension(196, 0)));
        addHolder.add(add);
        // removeHolder
        removeHolder.setLayout(new BoxLayout(removeHolder,
            BoxLayout.X_AXIS));
        removeHolder.add(Box.createRigidArea(new Dimension(35, 0)));
        removeHolder.add(fieldLabels[3]);
        removeHolder.add(Box.createRigidArea(new Dimension(196, 0)));
        removeHolder.add(remove);
        // background
        background.setLayout(new BoxLayout(background,
            BoxLayout.Y_AXIS));
        background.add(Box.createRigidArea(new Dimension(0, 30)));
        background.add(addHolder);
        background.add(Box.createRigidArea(new Dimension(0, 7)));
        background.add(labels);
        background.add(Box.createRigidArea(new Dimension(0, 1)));
        background.add(fields);
        background.add(Box.createRigidArea(new Dimension(0, 5)));
        background.add(data);
        background.add(Box.createRigidArea(new Dimension(0, 15)));
        background.add(removeHolder);
        background.add(Box.createRigidArea(new Dimension(0, 5)));
        background.add(userData);
        background.add(Box.createRigidArea(new Dimension(0, 15)));
        background.add(buttons);
        // content
        setLayout(new BorderLayout());
        add(background);
        setTitle("Subjects Chooser");
        setSize(500, 550);
        setLocationRelativeTo(null);
        setResizable(false);
    }// end of assemble()

    private void configure() {
        setModalityType(Dialog.ModalityType.APPLICATION_MODAL);
        data.setEditable(false);
        userData.setEditable(false);
        try {
            fetchCourses();
            fetchYears();
            fetchSemesters();

            data.connectDatabase(
                "com.mysql.jdbc.Driver",
                "jdbc:mysql://localhost:3306/requirementstrackerschema?allowMultiQueries=true",
                "root", "");
            userData
                .connectDatabase(
                    "com.mysql.jdbc.Driver",
                    "jdbc:mysql://localhost:3306/requirementstrackerschema?allowMultiQueries=true",
                    "root", "");
            data.setSelectSql("SELECT `subject`.subjectCode AS 'subject code', `subject`.descriptiveTitle AS 'descriptive title' FROM `subject` "
                + "WHERE `subject`.course = \""
                + (String) course.getSelectedItem()
                + "\""
                + "AND `subject`.year = \""
                + (String) year.getSelectedItem()
                + "\""
                + "AND `subject`.semester = \""
                + (String) semester.getSelectedItem() + "\"");

            userData
                .setSelectSql("SELECT subjectsselected.subjectCode AS 'subject code', "
                    + "subjectsselected.descriptiveTitle AS 'descriptive title' "
                    + "FROM subjectsselected NATURAL JOIN student "
                    + "WHERE student.isLoggedIn = 1");
            data.refresh();
            userData.refresh();

        } catch (Exception noise) {
            noise.printStackTrace();
        }

        (data.getColumnAsCreated(0)).setPreferredWidth(104);
        (data.getColumnAsCreated(1)).setPreferredWidth(300);
        (userData.getColumnAsCreated(0)).setPreferredWidth(104);
        (userData.getColumnAsCreated(1)).setPreferredWidth(300);

    }// end of configure()

    private void design() {

        // sizes
        fieldLabels[0].setPreferredSize(new Dimension(255, 28));
        fieldLabels[0].setMinimumSize(new Dimension(255, 28));
        fieldLabels[0].setMaximumSize(new Dimension(255, 28));
        fieldLabels[1].setPreferredSize(new Dimension(52, 28));
        fieldLabels[1].setMinimumSize(new Dimension(52, 28));
        fieldLabels[1].setMaximumSize(new Dimension(52, 28));
        fieldLabels[2].setPreferredSize(new Dimension(100, 28));
        fieldLabels[2].setMinimumSize(new Dimension(100, 28));
        fieldLabels[2].setMaximumSize(new Dimension(100, 28));
        fieldLabels[3].setPreferredSize(new Dimension(200, 28));
        fieldLabels[3].setMinimumSize(new Dimension(200, 28));
        fieldLabels[3].setMaximumSize(new Dimension(200, 28));
        fieldLabels[4].setPreferredSize(new Dimension(200, 28));
        fieldLabels[4].setMinimumSize(new Dimension(200, 28));
        fieldLabels[4].setMaximumSize(new Dimension(200, 28));
        course.setPreferredSize(new Dimension(255, 28));
        course.setMinimumSize(new Dimension(255, 28));
        course.setMaximumSize(new Dimension(255, 28));
        year.setPreferredSize(new Dimension(52, 28));
        year.setMinimumSize(new Dimension(52, 28));
        year.setMaximumSize(new Dimension(52, 28));
        semester.setPreferredSize(new Dimension(100, 28));
        semester.setMinimumSize(new Dimension(100, 28));
        semester.setMaximumSize(new Dimension(100, 28));
        okay.setPreferredSize(new Dimension(74, 28));
        okay.setMinimumSize(new Dimension(74, 28));
        okay.setMaximumSize(new Dimension(74, 28));
        add.setPreferredSize(new Dimension(28, 28));
        add.setMinimumSize(new Dimension(28, 28));
        add.setMaximumSize(new Dimension(28, 28));
        remove.setPreferredSize(new Dimension(28, 28));
        remove.setMinimumSize(new Dimension(28, 28));
        remove.setMaximumSize(new Dimension(28, 28));
        addHolder.setPreferredSize(new Dimension(500, 28));
        addHolder.setMinimumSize(new Dimension(500, 28));
        addHolder.setMaximumSize(new Dimension(500, 28));
        removeHolder.setPreferredSize(new Dimension(500, 28));
        removeHolder.setMinimumSize(new Dimension(500, 28));
        removeHolder.setMaximumSize(new Dimension(500, 28));
        labels.setPreferredSize(new Dimension(500, 28));
        labels.setMinimumSize(new Dimension(500, 28));
        labels.setMaximumSize(new Dimension(500, 28));
        fields.setPreferredSize(new Dimension(500, 28));
        fields.setMinimumSize(new Dimension(500, 28));
        fields.setMaximumSize(new Dimension(500, 28));
        data.setPreferredSize(new Dimension(424, 135));
        data.setMinimumSize(new Dimension(424, 135));
        data.setMaximumSize(new Dimension(424, 135));
        userData.setPreferredSize(new Dimension(424, 135));
        userData.setMinimumSize(new Dimension(424, 135));
        userData.setMaximumSize(new Dimension(424, 135));
        data.getColumn(0).setWidth(90);
        data.getColumn(1).setWidth(314);
        // borders
        course.setBorder(BorderFactory.createLineBorder(Color.BLACK,
            1));
        year.setBorder(BorderFactory.createLineBorder(Color.BLACK, 1));
        semester.setBorder(BorderFactory.createLineBorder(
            Color.BLACK, 1));
        // alignments
        fieldLabels[0].setAlignmentY(Component.BOTTOM_ALIGNMENT);
        fieldLabels[1].setAlignmentY(Component.BOTTOM_ALIGNMENT);
        fieldLabels[2].setAlignmentY(Component.BOTTOM_ALIGNMENT);
        fieldLabels[3].setAlignmentX(Component.CENTER_ALIGNMENT);
        // font styles
        Font font = new Font("Segoe UI Light", Font.PLAIN, 13);
        Font labelFont = new Font("Segoe UI", Font.BOLD, 16);
        Font bigLabelFont = new Font("Segoe UI", Font.BOLD, 20);
        Font buttonFont = new Font("Segoe UI", Font.BOLD, 13);
        Font tableHeaderFont = new Font("Segoe UI Semibold",
            Font.BOLD, 16);
        course.setFont(font);
        year.setFont(font);
        semester.setFont(font);
        okay.setFont(buttonFont);
        fieldLabels[0].setFont(labelFont);
        fieldLabels[1].setFont(labelFont);
        fieldLabels[2].setFont(labelFont);
        fieldLabels[3].setFont(bigLabelFont);
        fieldLabels[4].setFont(bigLabelFont);
        // backgrounds
        fields.setOpaque(false);
        labels.setOpaque(false);
        buttons.setOpaque(false);
        addHolder.setOpaque(false);
        removeHolder.setOpaque(false);
        course.setBackground(Color.WHITE);
        year.setBackground(Color.WHITE);
        semester.setBackground(Color.WHITE);
        // button styles
        add.setOpaque(false);
        add.setContentAreaFilled(false);
        add.setBorderPainted(false);
        remove.setOpaque(false);
        remove.setContentAreaFilled(false);
        remove.setBorderPainted(false);
        okay.setContentAreaFilled(false);
        // table skin
        data.setSkin(new ChooserTableSkin());
        userData.setSkin(new ChooserTableSkin());
        // borders
        data.setBorder(BorderFactory.createLineBorder(Color.BLACK, 1));
        userData.setBorder(BorderFactory.createLineBorder(
            Color.BLACK, 1));
        course.setBorder(BorderFactory.createLineBorder(Color.BLACK,
            1));
        year.setBorder(BorderFactory.createLineBorder(Color.BLACK, 1));
        semester.setBorder(BorderFactory.createLineBorder(
            Color.BLACK, 1));
    }// end of design()

    private void setTriggers() {

        MouseListener mouseTrigger = new MouseListener() {
            public void mouseClicked(MouseEvent me) {
                if (me.getSource() == add) {
                    verifyDistinctSubject();
                } else if (me.getSource() == remove) {
                    int selection = JOptionPane
                        .showOptionDialog(
                            null,
                            "All requirements and goals associated with this subject will be removed. Are you sure?",
                            "Confirm operation",
                            JOptionPane.YES_NO_OPTION,
                            JOptionPane.QUESTION_MESSAGE, null, null,
                            null);
                    switch (selection) {
                    case 0:
                        deselectSubject();
                        break;
                    }
                } else if (me.getSource() == okay) {
                    dispose();
                }
            } // end of mouseClicked()

            public void mouseEntered(MouseEvent me) {
                if (me.getSource() == add) {
                    add.setIcon(new ImageIcon(
                        "images\\icons\\subjectAddEntered.png"));
                }
                if (me.getSource() == remove) {
                    remove.setIcon(new ImageIcon(
                        "images\\icons\\removeEntered.png"));
                }
            } // end of mouseEntered()

            public void mouseExited(MouseEvent me) {
                if (me.getSource() == add) {
                    add.setIcon(new ImageIcon(
                        "images\\icons\\subjectAddNormal.png"));
                }
                if (me.getSource() == remove) {
                    remove.setIcon(new ImageIcon(
                        "images\\icons\\removeNormal.png"));
                }
            } // end of mouseExited()

            public void mousePressed(MouseEvent me) {
                if (me.getSource() == add) {
                    add.setIcon(new ImageIcon(
                        "images\\icons\\subjectAddPressed.png"));
                }
                if (me.getSource() == remove) {
                    remove.setIcon(new ImageIcon(
                        "images\\icons\\removePressed.png"));
                }
            } // end of mousePressed()

            public void mouseReleased(MouseEvent me) {
                if (me.getSource() == add) {
                    add.setIcon(new ImageIcon(
                        "images\\icons\\subjectAddNormal.png"));
                }
                if (me.getSource() == remove) {
                    remove.setIcon(new ImageIcon(
                        "images\\icons\\removeNormal.png"));
                }
            }
        };// end of mouseTrigger

        // combo boxes
        ActionListener actionTrigger = new ActionListener() {
            public void actionPerformed(ActionEvent anActionEvent) {
                try {
                    data.setSelectSql("SELECT `subject`.subjectCode AS 'subject code', "
                        + "`subject`.descriptiveTitle AS 'descriptive title' "
                        + "FROM `subject` "
                        + "WHERE `subject`.course = \""
                        + (String) course.getSelectedItem()
                        + "\""
                        + "AND `subject`.year = \""
                        + (String) year.getSelectedItem()
                        + "\""
                        + "AND `subject`.semester = \""
                        + (String) semester.getSelectedItem() + "\"");
                    data.refresh();
                    (data.getColumnAsCreated(0))
                        .setPreferredWidth(104);
                    (data.getColumnAsCreated(1))
                        .setPreferredWidth(300);
                    (userData.getColumnAsCreated(0))
                        .setPreferredWidth(104);
                    (userData.getColumnAsCreated(1))
                        .setPreferredWidth(300);
                } catch (Exception noise) {
                    noise.printStackTrace();
                }
            }
        };// end of viewerTrigger

        DBTableEventListener tableTrigger1 = new DBTableEventListener() {
            public boolean beforeRowSelectionChange(
                int fromRow, int toRow, int fromColumn, int toColumn) {
                data.setRowSelectionInterval(toRow, toRow);
                return false;
            }// end of beforeRowSelectionChange

            public boolean beforeColumnSelectionChange(
                int fromColumn, int toColumn, int fromRow, int toRow) {
                return true;
            }// end of beforeColumnSelectionChange

            public void afterRowSelectionChange(int fromRow, int toRow) {
            }// end of afterRowSelectionChange

            public void afterColumnSelectionChange(
                int fromColumn, int toColumn) {
            }// end of afterColumnSelectionChange

            public boolean onSortColumn(int column, boolean ascending) {
                return true;
            }// end of onSortColumn
        };// end of tableTrigger1

        DBTableEventListener tableTrigger2 = new DBTableEventListener() {
            public boolean beforeRowSelectionChange(
                int fromRow, int toRow, int fromColumn, int toColumn) {
                userData.setRowSelectionInterval(toRow, toRow);
                return false;
            }// end of beforeRowSelectionChange

            public boolean beforeColumnSelectionChange(
                int fromColumn, int toColumn, int fromRow, int toRow) {
                return true;
            }// end of beforeColumnSelectionChange

            public void afterRowSelectionChange(int fromRow, int toRow) {
            }// end of afterRowSelectionChange

            public void afterColumnSelectionChange(
                int fromColumn, int toColumn) {
            }// end of afterColumnSelectionChange

            public boolean onSortColumn(int column, boolean ascending) {
                return true;
            }// end of onSortColumn
        };// end of tableTrigger2

        add.addMouseListener(mouseTrigger);
        remove.addMouseListener(mouseTrigger);
        okay.addMouseListener(mouseTrigger);
        course.addActionListener(actionTrigger);
        year.addActionListener(actionTrigger);
        semester.addActionListener(actionTrigger);
        data.addDBTableEventListener(tableTrigger1);
        userData.addDBTableEventListener(tableTrigger2);
    }// end of setTriggers()

    public void refresh() {
        try {
            data.refresh();
            userData.refresh();
            (data.getColumnAsCreated(0)).setPreferredWidth(104);
            (data.getColumnAsCreated(1)).setPreferredWidth(300);
            (userData.getColumnAsCreated(0)).setPreferredWidth(104);
            (userData.getColumnAsCreated(1)).setPreferredWidth(300);
        } catch (Exception noise) {
            noise.printStackTrace();
        }
    }// end of refreshTable()

    private String getLoggedID() {
        prepareConnection();

        // get the studentID who is logged in
        String iDQuery = "SELECT studentID from requirementstrackerschema.student"
            + " WHERE isLoggedIn = 1";

        String loggedID = "";

        try {
            preparedStatement = connection.prepareStatement(iDQuery);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                loggedID = resultSet.getString("studentID");
            } else { // no one is loggedIn
                JOptionPane.showMessageDialog(null,
                    "Please login first.");
            } // end of if-else
        } catch (Exception e) {
            e.printStackTrace();
        } // end of try-catch

        return loggedID;
    } // end of getLoggedID method

    private void selectSubject() {
        int rowIndex = data.getSelectedRow();
        String subjectCodeData = data.getValueAt(rowIndex, 0)
            .toString();
        String descriptiveTitleData = data.getValueAt(rowIndex, 1)
            .toString();

        String createQuery = "INSERT INTO requirementstrackerschema.subjectsselected"
            + " VALUES "
            + "('"
            + getLoggedID()
            + "', '"
            + course.getSelectedItem().toString()
            + "', '"
            + subjectCodeData + "', '" + descriptiveTitleData + "')";

        try {
            DatabaseUtility.executeQuery(createQuery);
            userData.refresh();
            Explorer.getInstance().getRequirementsViewer().getData()
                .refresh();
            Explorer.getInstance().getGoalsViewer().getData()
                .refresh();
            Explorer.getInstance().getGoalsViewer().isDataEmpty();
            Explorer.getInstance().getRequirementsViewer()
                .isDataEmpty();
            (data.getColumnAsCreated(0)).setPreferredWidth(104);
            (data.getColumnAsCreated(1)).setPreferredWidth(300);
            (userData.getColumnAsCreated(0)).setPreferredWidth(104);
            (userData.getColumnAsCreated(1)).setPreferredWidth(300);
        } catch (Exception e) {
            e.printStackTrace();
        } // end of try-catch
    } // end of selectSubject method

    private void verifyDistinctSubject() {
        prepareConnection();

        int rowIndex = data.getSelectedRow();
        String subjectCodeData = data.getValueAt(rowIndex, 0)
            .toString();
        String descriptiveTitleData = data.getValueAt(rowIndex, 1)
            .toString();

        String query = "SELECT subjectCode, descriptiveTitle"
            + " FROM requirementstrackerschema.subjectsselected"
            + " WHERE subjectCode = ? AND descriptiveTitle = ?"
            + " AND studentID LIKE (SELECT studentID"
            + " FROM requirementstrackerschema.student"
            + " WHERE isLoggedIn = 1)";

        try {
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, subjectCodeData);
            preparedStatement.setString(2, descriptiveTitleData);

            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) { // do not add to subject selected table
                // do nothing
            } else {
                selectSubject();
            } // end of if-else

        } catch (Exception e) {
            e.printStackTrace();
        } // end of try-catch
    } // end of verifyDistinctSubject method

    private void deselectSubject() {
        int rowIndex = userData.getSelectedRow();
        String subjectCodeData = userData.getValueAt(rowIndex, 0)
            .toString();
        String descriptiveTitleData = userData
            .getValueAt(rowIndex, 1).toString();

        String deselectQuery = "DELETE FROM requirementstrackerschema.subjectsselected"
            + " WHERE studentID = "
            + getLoggedID()
            + " AND subjectCode = '"
            + subjectCodeData
            + "' AND descriptiveTitle = '"
            + descriptiveTitleData
            + "'";

        try {
            preparedStatement = connection
                .prepareStatement(deselectQuery);
            preparedStatement.executeUpdate();

            userData.refresh();
            Explorer.getInstance().getGoalsViewer().isDataEmpty();
            Explorer.getInstance().getRequirementsViewer()
                .isDataEmpty();
            (data.getColumnAsCreated(0)).setPreferredWidth(104);
            (data.getColumnAsCreated(1)).setPreferredWidth(300);
            (userData.getColumnAsCreated(0)).setPreferredWidth(104);
            (userData.getColumnAsCreated(1)).setPreferredWidth(300);

        } catch (Exception e) {
            e.printStackTrace();
        } // end of try-catch

    } // end of selectSubject method

    private void prepareConnection() {
        try {
            connection = DatabaseUtility.prepareConnection();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
    } // end of prepareConnection method

    public void fetchCourses() throws Exception {

        course.removeAllItems();
        DatabaseUtility.prepareConnection();
        String[][] temp = DatabaseUtility
            .getResults("SHOW COLUMNS from SUBJECT "
                + "WHERE field = 'course'");
        temp[0][1] = temp[0][1].substring(5,
            (temp[0][1].length() - 1));
        temp[0][1] = temp[0][1].replaceAll("'", "");
        for (int row = 0; row < (temp[0][1].split(",")).length; row++) {
            course.addItem((Arrays.asList(temp[0][1].split(",")))
                .get(row));
        }
    }// end of fetchCourses()

    public void fetchYears() throws Exception {

        year.removeAllItems();
        DatabaseUtility.prepareConnection();
        String[][] temp = DatabaseUtility
            .getResults("SHOW COLUMNS from SUBJECT "
                + "WHERE field = 'year'");
        temp[0][1] = temp[0][1].substring(5,
            (temp[0][1].length() - 1));
        temp[0][1] = temp[0][1].replaceAll("'", "");
        for (int row = 0; row < (temp[0][1].split(",")).length; row++) {
            year.addItem((Arrays.asList(temp[0][1].split(",")))
                .get(row));
        }
    }// end of fetchYears()

    public void fetchSemesters() throws Exception {

        semester.removeAllItems();
        DatabaseUtility.prepareConnection();
        String[][] temp = DatabaseUtility
            .getResults("SHOW COLUMNS from SUBJECT "
                + "WHERE field = 'semester'");
        temp[0][1] = temp[0][1].substring(5,
            (temp[0][1].length() - 1));
        temp[0][1] = temp[0][1].replaceAll("'", "");
        for (int row = 0; row < (temp[0][1].split(",")).length; row++) {
            semester.addItem((Arrays.asList(temp[0][1].split(",")))
                .get(row));
        }
    }// end of fetchYears()

    public DBTable getData() {
        return data;
    }

    public DBTable getUserData() {
        return userData;
    }
}// end of SubjectsChooser